﻿using Anke.SHManage.Model.FixedAssetsModel;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL.FixedAssetsDAL
{
    public class FixedAssetsRepairDAL
    {
        /// <summary>
        /// 获取固定资产维修记录列表
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="assetTypeID"></param>
        /// <param name="materialID"></param>
        /// <param name="assetID"></param>
        /// <param name="DateStart"></param>
        /// <param name="DateEnd"></param>
        /// <returns></returns>
        public object GetRepairList(int pageSize, int pageIndex, string assetTypeID, string materialID, string assetID, DateTime DateStart, DateTime DateEnd)
        {
            List<FIXED_RepairInfo> list = new List<FIXED_RepairInfo>();
            StringBuilder sb = new StringBuilder();
            sb.Append(" select distinct identity(int,1,1) as 行号 ");
            sb.Append(" ,fr.编码,fr.资产编码,fr.维修日期,fr.故障情况,fr.故障处理,fr.申报人,fr.维修工时费,fr.材料配件费,fr.合计金额 ");
            sb.Append(" ,fr.送修科室,fr.经办人,fr.科室负责人,fr.维修工程师,fr.维修单位,fr.联系电话,fr.填报日期 ");
            sb.Append(" ,资产名称=fm.资产名称,固定资产编号=fm.固定资产编码,当前仓库=fs.仓库名称,主机序列号=fm.主机序列号 ");
            sb.Append(" into #temp ");
            sb.Append(@"   
                from FIXED_Repair fr ");
            sb.Append(@"   
                left join FIXED_Matetial fm on fm.编码=fr.资产编码 ");
            sb.Append(@"   
                left join FIXED_Storage fs on fs.仓库编码=fm.当前仓库编码 ");
            sb.Append(@"
                where 1=1 ");
            //分类筛选
            //WhereClauseUtility.AddStringEqual("类型编码", assetTypeID, sb);
            if ((assetTypeID.Length > 0) && (assetTypeID != null) && (assetTypeID != "-1") && (assetTypeID != "--请选择--"))
            {
                List<string> typeIDList = GetTDicStrListByID(assetTypeID); //分类编码list
                //string s1 = string.Join(",", typeIDList.ToArray()); //将list转为string，逗号分隔
                string s1 = "'" + string.Join("','", typeIDList.ToArray()) + "'";
                WhereClauseUtility.AddInSelectQuery("fm.类型编码", s1, sb);
            }
            //资产编码筛选
            if ((materialID != null) && (materialID != "") && (materialID != "-1") && materialID != "--请选择--")
            {
                Guid MaterialID = new Guid(materialID);
                sb.Append(@" and fr.资产编码='" + MaterialID + "'");
            }
            WhereClauseUtility.AddStringEqual("fm.固定资产编码", assetID, sb);

            WhereClauseUtility.AddDateTimeGreaterThan("维修日期", DateStart, sb);
            WhereClauseUtility.AddDateTimeLessThan("维修日期", DateEnd, sb);

            sb.Append(@"
                order by 维修日期,填报日期,资产名称 ");
            sb.Append(@"
                    select top " + pageSize + " A.*  from #temp A where 行号>" + (pageIndex - 1) * pageSize + " order by 行号 ");
            sb.Append(@"
                    SELECT count(*) FROM #temp t
                    drop table #temp ");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        FIXED_RepairInfo info = new FIXED_RepairInfo();
                        info.BianMa = Convert.ToInt32(dr["编码"].ToString());
                        info.ZiChanBianMa = new Guid(dr["资产编码"].ToString());
                        info.WeiXiuRiQi = Convert.ToDateTime(dr["维修日期"].ToString());
                        info.GuZhangQingKuang = dr["故障情况"].ToString();
                        info.GuZhangChuLi = dr["故障处理"].ToString();
                        info.ShenBaoRen = dr["申报人"].ToString();
                        info.WeiXiuGongShiFei = Convert.ToDouble(dr["维修工时费"].ToString());
                        info.CaiLiaoPeiJianFei = Convert.ToDouble(dr["材料配件费"].ToString());
                        info.HeJiJinE = Convert.ToDouble(dr["合计金额"].ToString());
                        info.SongXiuKeShi = dr["送修科室"].ToString();
                        info.JingBanRen = dr["经办人"].ToString();
                        info.KeShiFuZeRen = dr["科室负责人"].ToString();
                        info.WeiXiuGongChengShi = dr["维修工程师"].ToString();
                        info.WeiXiuDanWei = dr["维修单位"].ToString();
                        info.LianXiDianHua = dr["联系电话"].ToString();
                        info.TianBaoRiQi = Convert.ToDateTime(dr["填报日期"].ToString());

                        info.ZiChanMingCheng = dr["资产名称"].ToString();
                        info.GuDingZiChanBianHao = dr["固定资产编号"].ToString();
                        info.DangQianCangKu = dr["当前仓库"].ToString();
                        info.ZhuJiXuLieHao = dr["主机序列号"].ToString();

                        list.Add(info);
                    }
                }
                int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                var result = new { total = total, rows = list };
                return result;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsRepairDAL/GetRepairList", ex.Message);
                return null;
            }
        }


        /// <summary>
        /// 递归查询该分类ID下的所有list（分类及后代分类）-适用于树结构数据
        /// </summary>
        /// <param name="assetTypeID"></param>
        /// <returns></returns>
        private List<string> GetTDicStrListByID(string assetTypeID)
        {
            List<string> list = new List<string>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"with dic 
                            as( select * 
                                from TDictionary 
                                where ID in ('" + assetTypeID + @"')
                                union all 
                                select TDictionary.* 
                                from dic , TDictionary 
                                where dic.id = TDictionary.ParentID
                            )
                          select * from dic");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        list.Add(dr["ID"].ToString());
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsRepairDAL/GetTDicStrListByID", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 根据编码获取资产维修数据
        /// </summary>
        /// <param name="Code"></param>
        /// <returns></returns>
        public FIXED_RepairInfo GetRepairData(string Code)
        {
            StringBuilder sb = new StringBuilder();
            //Guid Code1 = new Guid(Code);
            int Code1 = Convert.ToInt32(Code);
            sb.Append(@" select fr.* ");
            sb.Append(@" from FIXED_Repair fr ");
            sb.Append(@" 
                        where 1=1 ");
            //sb.Append(" and 编码='" + Code1 + "'");
            sb.Append(" and 编码=" + Code1 + "");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                DataTable dt = ds.Tables[0];
                var query = from a in dt.AsEnumerable()
                            select new FIXED_RepairInfo()
                            {
                                BianMa = a.Field<int>("编码"),
                                ZiChanBianMa = a.Field<Guid>("资产编码"),
                                WeiXiuRiQi = a.Field<DateTime>("维修日期"),
                                GuZhangQingKuang = a.Field<string>("故障情况"),
                                GuZhangChuLi = a.Field<string>("故障处理"),
                                ShenBaoRen = a.Field<string>("申报人"),
                                WeiXiuGongShiFei = a.Field<Double>("维修工时费"),
                                CaiLiaoPeiJianFei = a.Field<Double>("材料配件费"),
                                HeJiJinE = a.Field<Double>("合计金额"),
                                SongXiuKeShi = a.Field<string>("送修科室"),
                                JingBanRen = a.Field<string>("经办人"),
                                KeShiFuZeRen = a.Field<string>("科室负责人"),
                                WeiXiuGongChengShi = a.Field<string>("维修工程师"),
                                WeiXiuDanWei = a.Field<string>("维修单位"),
                                LianXiDianHua = a.Field<string>("联系电话"),
                                TianBaoRiQi = a.Field<DateTime>("填报日期")
                            };
                var result = query.FirstOrDefault();
                return result;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsRepairDAL/GetRepairData", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 新增资产维修数据
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool InsertEditPageData(FIXED_RepairInfo info)
        {
            bool result = true;
            StringBuilder sb = new StringBuilder();
            info.BianMa = GetMaxID("FIXED_Repair") + 1;//获取表的编码最大值+1
            sb.Append(" insert into FIXED_Repair values (");
            sb.Append(" " + info.BianMa + ", ");
            sb.Append(" '" + info.ZiChanBianMa + "', ");
            sb.Append(" '" + info.WeiXiuRiQi + "', ");
            sb.Append(" '" + info.GuZhangQingKuang + "', ");
            sb.Append(" '" + info.GuZhangChuLi + "', ");
            sb.Append(" '" + info.ShenBaoRen + "', ");
            sb.Append(" " + info.WeiXiuGongShiFei + ", ");
            sb.Append(" " + info.CaiLiaoPeiJianFei + ", ");
            sb.Append(" " + info.HeJiJinE + ", ");
            sb.Append(" '" + info.SongXiuKeShi + "', ");
            sb.Append(" '" + info.JingBanRen + "', ");
            sb.Append(" '" + info.KeShiFuZeRen + "', ");
            sb.Append(" '" + info.WeiXiuGongChengShi + "', ");
            sb.Append(" '" + info.WeiXiuDanWei + "', ");
            sb.Append(" '" + info.LianXiDianHua + "', ");
            sb.Append(" '" + info.TianBaoRiQi + "' ");
            sb.Append(" )");

            try
            {
                int intRow = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                return intRow > 0;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsRepairDAL/InsertEditPageData", ex.Message);
                result = false;
            }
            return result;
        }

        /// <summary>
        /// 获取表中编码最大值
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        private int GetMaxID(string tablename)
        {
            int result = 0;
            StringBuilder sb = new StringBuilder();
            sb.Append(@" select 编码 from " + tablename + " where 编码 in (select max(cast(编码 as int)) from " + tablename + ") ");
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                if (dr.Read())
                {
                    result = Convert.ToInt32(dr["编码"]);
                }
                return result;
            }
        }

        /// <summary>
        /// 修改资产维修数据
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool UpdateEditPageData(FIXED_RepairInfo info)
        {
            bool result = true;
            StringBuilder sb = new StringBuilder();
            sb.Append(" update FIXED_Repair set ");
            sb.Append(@" 
                        维修日期='" + info.WeiXiuRiQi + "', ");
            sb.Append(" 故障情况='" + info.GuZhangQingKuang + "', ");
            sb.Append(" 故障处理='" + info.GuZhangChuLi + "', ");
            sb.Append(" 申报人='" + info.ShenBaoRen + "', ");
            sb.Append(" 维修工时费=" + info.WeiXiuGongShiFei + ", ");
            sb.Append(" 材料配件费=" + info.CaiLiaoPeiJianFei + ", ");
            sb.Append(" 合计金额=" + info.HeJiJinE + ", ");
            sb.Append(" 送修科室='" + info.SongXiuKeShi + "', ");
            sb.Append(" 经办人='" + info.JingBanRen + "', ");
            sb.Append(" 科室负责人='" + info.KeShiFuZeRen + "', ");
            sb.Append(" 维修工程师='" + info.WeiXiuGongChengShi + "', ");
            sb.Append(" 维修单位='" + info.WeiXiuDanWei + "', ");
            sb.Append(" 联系电话='" + info.LianXiDianHua + "', ");
            sb.Append(" 填报日期='" + info.TianBaoRiQi + "' ");
            sb.Append(@" 
                        where 1=1 ");
            sb.Append(@" 
                        and 编码= " + info.BianMa + "");
            try
            {
                int intRow = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                return intRow > 0;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsRepairDAL/UpdateEditPageData", ex.Message);
                result = false;
            }
            return result;
        }
    }
}
